<?php

class IndividualController extends BackendController
{
    /**
     * PHPExcel 数据导出
     */
    public function actionPhpexcel() {

        $columns = [
            [
                'table' => 'member',
                'column' => 'status',
                'label' => '状态',
            ],
            // [
            //     'table' => 'individualmember',
            //     'column' => 'company',
            //     'label' => '单位名称',
            // ],
            [
                'table' => 'individualmember',
                'column' => 'name',
                'label' => '姓名',
            ],
            [
                'table' => 'individualmember',
                'column' => 'bianhao',
                'label' => '编号',
            ],
            [
                'table' => 'member',
                'column' => 'username',
                'label' => '登录用户名',
            ],
            [
                'table' => 'member',
                'column' => 'telphone',
                'label' => '联系手机',
            ],
            [
                'table' => 'member',
                'column' => 'xh_id',
                'label' => '总会或分会',
            ],
            [
                'table' => 'member',
                'column' => 'shenqingtime',
                'label' => '申请时间',
            ],
            [
                'table' => 'member',
                'column' => 'applytime',
                'label' => '入会时间',
            ],
            [
                'table' => 'member',
                'column' => 'endtime',
                'label' => '到期时间',
            ],
            [
                'table' => 'member',
                'column' => 'grade',
                'label' => '会员等级',
            ],
            [
                'table' => 'individualmember',
                'column' => 'loc_province',
                'label' => '省',
            ],
            [
                'table' => 'individualmember',
                'column' => 'loc_city',
                'label' => '市',
            ],
            [
                'table' => 'individualmember',
                'column' => 'loc_town',
                'label' => '县',
            ],
            [
                'table' => 'individualmember',
                'column' => 'sex',
                'label' => '性别',
            ],
            [
                'table' => 'individualmember',
                'column' => 'technicaltitle',
                'label' => '职称',
            ],
            [
                'table' => 'individualmember',
                'column' => 'telphone',
                'label' => '联系电话(座机)',
            ],
            [
                'table' => 'individualmember',
                'column' => 'idnumber',
                'label' => '证件号码',
            ],
            [
                'table' => 'individualmember',
                'column' => 'party',
                'label' => '党派',
            ],
            [
                'table' => 'individualmember',
                'column' => 'major',
                'label' => '系或专业',
            ],
            [
                'table' => 'individualmember',
                'column' => 'education',
                'label' => '学历',
            ],
            [
                'table' => 'individualmember',
                'column' => 'birthday',
                'label' => '出生日期',
            ],
            [
                'table' => 'member',
                'column' => 'fenhuiloveone',
                'label' => '关注的分会1',
            ],
            [
                'table' => 'member',
                'column' => 'fenhuilovetwo',
                'label' => '关注的分会2',
            ],
            [
                'table' => 'individualmember',
                'column' => 'idtype',
                'label' => '证件类型',
            ],
            [
                'table' => 'individualmember',
                'column' => 'tuijianren',
                'label' => '推荐人',
            ],
            [
                'table' => 'individualmember',
                'column' => 'isgongkai',
                'label' => '公开个人信息',
            ],
            [
                'table' => 'individualmember',
                'column' => 'address',
                'label' => '通信地址',
            ],
            // [
            //     'table' => 'individualmember',
            //     'column' => 'isgongcheng',
            //     'label' => '是否工程院院士',
            // ],
            // [
            //     'table' => 'individualmember',
            //     'column' => 'iskexue',
            //     'label' => '是否科学院院士',
            // ],
            // [
            //     'table' => 'individualmember',
            //     'column' => 'qikanselect',
            //     'label' => '所选期刊',
            // ],
            [
                'table' => 'individualmember',
                'column' => 'workunit',
                'label' => '工作单位',
            ],
            // [
            //     'table' => 'individualmember',
            //     'column' => 'preworkunit',
            //     'label' => '个人简历',
            // ],
            // [
            //     'table' => 'individualmember',
            //     'column' => 'beizhu',
            //     'label' => '备注',
            // ],
            // [
            //     'table' => 'individualmember',
            //     'column' => 'zytechang',
            //     'label' => '专业特长',
            // ],
            // [
            //     'table' => 'individualmember',
            //     'column' => 'inventionpatent',
            //     'label' => '业绩成果',
            // ],
            [
                'table' => 'member',
                'column' => 'email',
                'label' => '邮箱',
            ],
        ];
        define('EOL', '<br />');

        // echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;

        // die('ok');
        $gradeArr = Grade::find()->select('name')->asArray()->indexBy('id')->column();
        $xhArr = Fenhuigrade::find()->select('name')->asArray()->indexBy('id')->column();
        $statusArr = Member::getArrayStatus();

        $objPHPExcel = new \PHPExcel();
        ini_set("memory_limit", "1024M"); // 设置php可使用内存
        set_time_limit(0);  # 设置执行时间最大值

        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            // die($cacheMethod . " 缓存方法不可用" . EOL);
        }

        // echo date('H:i:s'), " 当前使用的缓存方法是： ", $cacheMethod, " 方式", EOL;
        // echo date('H:i:s'), " 开始设置文档属性", EOL;
        // $objPHPExcel->getProperties()->setCreator("dcb3688")
        //         ->setLastModifiedBy("dcb3688")
        //         ->setTitle("客户信息记录")
        //         ->setSubject("客户信息Document")
        //         ->setDescription("描述……")
        //         ->setKeywords("office 2007  php")
        //         ->setCategory("产品信息AAA");


        // echo date('H:i:s'), " 开始添加单元格标题", EOL;
        // $objPHPExcel->setActiveSheetIndex(0);
        // $zimu = 'A';
        // foreach ($columns as $key => $value) {
        //     // $objPHPExcel->getActiveSheet()->setCellValue($zimu .'1', $value['label']);
        //     $zimu++;
        // }

        /**
         * 单元格宽度
         */
        // $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
        // $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(45);
        // $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
        // $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
        // $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
        
        /**
         * 左对齐与 右对齐
         * 可设置整列->getStyle('N')   可针对行rows设置getStyle('N3')
         */
        // $objPHPExcel->getActiveSheet()->getStyle('N')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        // $objPHPExcel->getActiveSheet()->getStyle('N3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);





        #####################################开始添加数据###############################################################
        
        /**
         * 分页分时间： 微软Execl最大值6万行， total/60000=文件个数，  limit 60000, 60000
         * $model->find()->offset($pages->offset)->limit(60000)->all()
         */
        // die('ok1111');

        $model = Individualmember::find()->select('
            individualmember.name,
            individualmember.bianhao,
            individualmember.loc_province,
            individualmember.loc_city,
            individualmember.loc_town,
            individualmember.sex,
            individualmember.technicaltitle,
            individualmember.telphone,
            individualmember.idnumber,
            individualmember.party,
            individualmember.major,
            individualmember.education,
            individualmember.birthday,
            individualmember.idtype,
            individualmember.tuijianren,
            individualmember.isgongkai,
            individualmember.address,
            individualmember.workunit,
            member.status,
            member.username,
            member.telphone,
            member.xh_id,
            member.shenqingtime,
            member.applytime,
            member.endtime,
            member.grade,
            member.fenhuiloveone,
            member.fenhuilovetwo,
            member.email
            ')->joinWith('member')->orderBy('individualmember.id asc')->limit(10)->asArray()->all();


        // echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        $ind = 0;
        $newdataArr = [];
        if (!empty($model)) {
            foreach ($model as $key => $value) {
                // echo $key .'_'. date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
                $Cellkey = $key + 2;
                $blood = [1 => 'A型', 2 => 'B型', 3 => 'AB型', 4 => 'O型'];
                $zimu = 'A';
                foreach ($columns as $col_key => $col_value) {
                    // echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;

                    $new_col_name = $col_value['column'];

                    $newvalue = $value[$new_col_name];
                    if ($col_value['column'] == 'shenqingtime'
                         || $col_value['column'] == 'applytime'
                         || $col_value['column'] == 'endtime'
                         || $col_value['column'] == 'birthday'
                         ) {
                        $newvalue = $newvalue ? date('Y-m-d H:i:s', $newvalue) : '';
                    } elseif ($col_value['column'] == 'xh_id') {
                        $newvalue = $newvalue ? (isset($xhArr[$newvalue]) ? $xhArr[$newvalue] : '') : '电子学会总会';
                    } elseif ($col_value['column'] == 'grade') {
                        $newvalue = $newvalue ? $gradeArr[$newvalue] : '';
                    } elseif ($col_value['column'] == 'status') {
                        $newvalue = $newvalue ? $statusArr[$newvalue] : '';
                    } elseif ($col_value['column'] == 'fenhuiloveone') {
                        $newvalue = $newvalue != '请选择' ? (isset($xhArr[$newvalue]) ? $xhArr[$newvalue] : '') : '';
                    } elseif ($col_value['column'] == 'fenhuilovetwo') {
                        $newvalue = $newvalue != '请选择' ? (isset($xhArr[$newvalue]) ? $xhArr[$newvalue] : '') : '';
                    }
                    $model[$key][$new_col_name] = $newvalue;
                    // $objPHPExcel->getActiveSheet()->setCellValue($zimu . $Cellkey, $newvalue);
                    $zimu++;
                }
                $ind++;
            }
        } else {
            die(" 暂无数据" . EOL);
        }

        return json_encode(['label' => array_column($columns, 'label'), 'data' => $model]);
        #######################################################################################################

        // $objPHPExcel->getActiveSheet()->setTitle('客户信息');
        // echo date('H:i:s'), " 设置格式为Excel2007版格式", EOL;
        // $callStartTime = microtime(true);

        // header('pragma:public');
        // header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . '个人会员' . '.xlsx"');
        // header("Content-Disposition:attachment;filename=" . '个人会员' . ".xlsx"); //attachment新窗口打印inline本窗口打印
        
        // $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        // $objWriter->save('php://output');


        // $callEndTime = microtime(true);
        // $callTime = $callEndTime - $callStartTime;

        // echo date('H:i:s'), " 设置生成的文件为： ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
        // echo date('H:i:s'), ' 写入Workbook中耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
        // echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        // echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
        // echo date('H:i:s'), " 完成写入文件", EOL;
        // echo date('H:i:s'), ' 文件被创建在： ', getcwd(), '目录', EOL;
    }

    public function actionExcel()
    {
        return $this->render('phpexcel');
    }
}
